package org.springblade.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.tool.utils.StringUtil;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description
 * @Author ysq
 * @Date 2023/3/23
 **/
public class PoiExcelUtil {

	/**
	 * 读取excel文件
	 * @param wb
	 * @param sheetIndex sheet页下标：从0开始
	 * @param header 标题行,从0开始
	 * @param startReadLine 开始读取的行:从0开始
	 * @param tailLine 去除最后读取的行
	 */
	public static List<Map<String,Object>> readExcel(Workbook wb, int sheetIndex, int header, int startReadLine, int tailLine) {
		Sheet sheet = wb.getSheetAt(sheetIndex);
		// 标题栏
		Row headerRow = sheet.getRow(header);
		if (headerRow == null) {
			throw new ServiceException("请使用正确的模板导入数据");
		}
		Map<String, Object> headerMap = getRowData(sheet, headerRow, null);

		// 数据
		Row row = null;
		List<Map<String,Object>> result = new ArrayList<>();
		for(int i = startReadLine; i < sheet.getLastRowNum()-tailLine + 1; i++) {
			row = sheet.getRow(i);
			Map<String, Object> map = getRowData(sheet, row, headerMap);
			if (map.size() > 0) {
				headerMap.forEach((k,v) -> {
					if(StringUtil.isNotBlank(String.valueOf(v)) && !map.containsKey(String.valueOf(v))){
						map.put(String.valueOf(v),"");
					}
				});
				result.add(map);
			}
		}
		return result;
	}

	/**
	 * 获取每行数据
	 * @param sheet
	 * @param row
	 * @param headerMap 标题栏
	 * @return
	 */
	public static Map<String, Object> getRowData(Sheet sheet, Row row, Map<String, Object> headerMap) {
		Map<String,Object> map = new HashMap<>(16);
		for (int i = 0; i < row.getLastCellNum(); i++) {
			Cell c = row.getCell(i);
			if (c == null) {
				continue;
			}
			String returnStr = "";
			boolean isMerge = isMergedRegion(sheet,  row.getRowNum(), c.getColumnIndex());
			//判断是否具有合并单元格
			if(isMerge) {
				String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
				returnStr = rs;
			}else {
				returnStr = getCellValue(c);
			}
			map.put(headerMap == null ? String.valueOf(i) : String.valueOf(headerMap.get(String.valueOf(i))), returnStr);
		}
		return map;
	}

	/**
	 * 获取合并单元格的值
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public static String getMergedRegionValue(Sheet sheet ,int row , int column){
		int sheetMergeCount = sheet.getNumMergedRegions();

		for(int i = 0 ; i < sheetMergeCount ; i++){
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if(row >= firstRow && row <= lastRow){

				if(column >= firstColumn && column <= lastColumn){
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell) ;
				}
			}
		}

		return null ;
	}

	/**
	 * 判断合并了行
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private boolean isMergedRow(Sheet sheet,int row ,int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if(row == firstRow && row == lastRow){
				if(column >= firstColumn && column <= lastColumn){
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * @param sheet
	 * @param row 行下标
	 * @param column 列下标
	 * @return
	 */
	public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if(row >= firstRow && row <= lastRow){
				if(column >= firstColumn && column <= lastColumn){
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 获取单元格的值
     * @param cell
     * @return
	 */
	public static String getCellValue(Cell cell){
		if(cell == null) {
			return "";
		}

		String cellValue = null;
		switch (cell.getCellTypeEnum()) {
			case NUMERIC:
				// 判断是否是日期
				if (DateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
					break;
				} else {
					// 兼容int和double类型
					cellValue = String.valueOf(cell.getNumericCellValue());
					if (cellValue.endsWith(".0")) {
						cellValue = cellValue.substring(0, cellValue.length() - 2);
					} else if (cellValue.contains("E")) {
						cellValue = new BigDecimal(cellValue).toPlainString();
					}
					break;
				}
			case STRING:
				cellValue = cell.getStringCellValue();
				break;
			case FORMULA:
				cellValue = cell.getNumericCellValue() + "";
				break;
			case BLANK:
				cellValue = "";
				break;
			case BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case ERROR:
				cellValue = String.valueOf(cell.getErrorCellValue());
				break;
			default:
				break;
		}

		return cellValue;
	}

}
